package com.todod.common.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.artofsolving.jodconverter.openoffice.connection.OpenOfficeConnection;
import com.artofsolving.jodconverter.openoffice.connection.SocketOpenOfficeConnection;
import com.lowagie.text.Document;
import com.lowagie.text.pdf.PdfCopy;
import com.lowagie.text.pdf.PdfImportedPage;
import com.lowagie.text.pdf.PdfReader;
import com.todod.exception.MergePdfFilesException;

public class ReadFileTest {

	public static final String LOCAL_HOST_XLS = "127.0.0.1";
	public static final int LOCAL_PORT_XLS = 8100;

	public static final String LOCAL_HOST = "127.0.0.1";
	public static final int LOCAL_PORT = 8100;

	public static void readExcelXls(String inputFilePath, String outputFilePath, InputStream in, String fileEnd) {
		// 获取文件名
		String fileName = inputFilePath.substring(0, inputFilePath.lastIndexOf("."));
		HSSFWorkbook workbook;
		try {
			workbook = new HSSFWorkbook(in);

			int colWidth = 0;
			int rows = 0;
			int coloumNum = 0;
			int total = workbook.getNumberOfSheets();

			String files = "";
			String comma = "";
			for (int i = 0; i < total; i++) {// 获取每个Sheet表
				Sheet sheet = workbook.getSheetAt(i);
				int lastRowNum = sheet.getLastRowNum();
				if (lastRowNum > 0) {

					if (sheet.getRow(0) != null) {
						coloumNum = sheet.getRow(0).getPhysicalNumberOfCells() + 1;
					} else if (sheet.getRow(1) != null) {
						coloumNum = sheet.getRow(1).getPhysicalNumberOfCells() + 1;
					} else if (sheet.getRow(2) != null) {
						coloumNum = sheet.getRow(2).getPhysicalNumberOfCells() + 1;
					} else if (sheet.getRow(3) != null) {
						coloumNum = sheet.getRow(3).getPhysicalNumberOfCells() + 1;
					} else if (sheet.getRow(4) != null) {
						coloumNum = sheet.getRow(4).getPhysicalNumberOfCells() + 1;
					} else if (sheet.getRow(5) != null) {
						coloumNum = sheet.getRow(5).getPhysicalNumberOfCells() + 1;
					} else if (sheet.getRow(6) != null) {
						coloumNum = sheet.getRow(6).getPhysicalNumberOfCells() + 1;
					} else if (sheet.getRow(7) != null) {
						coloumNum = sheet.getRow(7).getPhysicalNumberOfCells() + 1;
					}

					colWidth = sheet.getColumnWidth(i);// 获取列宽
					rows = sheet.getLastRowNum() + 1;// 不作处理时获取的行数
					// rows = sheet.getPhysicalNumberOfRows();// 获取物理行数
					// rows = readExcelValueRows(sheet);// 获取Excel工作簿的真实行数（非空行，即有数据的行）
					// rows = getExcelRealRow(sheet);// 获取excel表格的真实行数

					// 拆分sheet页生成新的Excel文件
					String filePath2 = fileName + "" + i + "." + fileEnd + "";
					copyFile(inputFilePath, filePath2);
					File file = new File(filePath2);
					HSSFWorkbook workbook2 = new HSSFWorkbook(new FileInputStream(file));
					int total2 = workbook2.getNumberOfSheets();
					for (int j = total2 - 1; j >= 0; j--) {
						if (i == j) {
							continue;
						}
						workbook2.removeSheetAt(j);
					}
					String filePath3 = fileName + "_" + i + "." + fileEnd + "";
					String pdfPath3 = fileName + "_" + i + ".pdf";
					File file1 = new File(filePath3);
					FileOutputStream fout = new FileOutputStream(filePath3);
					workbook2.write(fout);
					workbook2.close();
					fout.close();

					OpenOfficeConnection connection = new SocketOpenOfficeConnection(LOCAL_HOST_XLS, LOCAL_PORT_XLS);
					connection.connect();

					int rowsHeight = 0;
					if (rows < 9) {
						rowsHeight = rows * 650;
					} else if (rows >= 9 && rows <= 10) {
						rowsHeight = rows * 550;
					} else if (rows > 10 && rows <= 20) {
						rowsHeight = rows * 550;
					} else if (rows > 20 && rows <= 30) {
						rowsHeight = rows * 380;
					} else if (rows > 30 && rows <= 50) {
						rowsHeight = rows * 325;
					} else if (rows > 50 && rows <= 100) {
						rowsHeight = rows * 325;
					} else if (rows > 100 && rows <= 150) {
						rowsHeight = rows * 305;
					} else if (rows > 150 && rows <= 200) {
						rowsHeight = rows * 305;
					} else if (rows > 200 && rows <= 300) {
						rowsHeight = rows * 295;
					} else if (rows > 300 && rows <= 400) {
						rowsHeight = rows * 295;
					} else if (rows > 400 && rows <= 500) {
						rowsHeight = rows * 295;
					} else if (rows > 500 && rows <= 600) {
						rowsHeight = rows * 295;
					} else {
						rowsHeight = rows * 265;
					}

					int coloumWidth = (coloumNum * colWidth) * 3;
					if (coloumWidth <= 21000) {
						coloumWidth = 21000;
					} else if (coloumWidth > 21000 && coloumWidth <= 29700) {
						coloumWidth = 29700;
					} else if (coloumWidth > 29700 && coloumWidth <= 42000) {
						coloumWidth = 42000;
					} else {
						coloumWidth = 54000;
					}

//					System.out.println("总列数----------------" + coloumNum);
//					System.out.println("列宽----------------" + colWidth);
//					System.out.println("行数----------------" + rows);
//					System.out.println("总宽度----------------" + coloumWidth);
//					System.out.println("总行高----------------" + rowsHeight);

					ConverterDocument converterDocument = new ConverterDocument(connection, coloumWidth, rowsHeight);
					converterDocument.convert(new File(filePath3), new File(pdfPath3));

					connection.disconnect();

					file.delete();// 删除文件
					file1.delete();// 删除文件

					String pdfPath4 = pdfPath3.replace("/", File.separator);
					files = files + comma + "" + pdfPath4.substring(0, pdfPath4.length()) + "";
					comma = ",";
				}
			}

			String[] filess = files.split(",");
			try {
				mergePdfFiles(filess, outputFilePath);
			} catch (MergePdfFilesException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static void readExcelXlsx(String inputFilePath, String outputFilePath, InputStream in, String fileEnd) {
		// 获取文件名
		String fileName = inputFilePath.substring(0, inputFilePath.lastIndexOf("."));
		XSSFWorkbook workbook;
		try {
			workbook = new XSSFWorkbook(in);

			int colWidth = 0;
			int rows = 0;
			int coloumNum = 0;
			int total = workbook.getNumberOfSheets();

			String files = "";
			String comma = "";
			for (int i = 0; i < total; i++) {// 获取每个Sheet表
				Sheet sheet = workbook.getSheetAt(i);

				if (sheet.getRow(0) != null) {
					coloumNum = sheet.getRow(0).getPhysicalNumberOfCells() + 1;
				} else if (sheet.getRow(1) != null) {
					coloumNum = sheet.getRow(1).getPhysicalNumberOfCells() + 1;
				} else if (sheet.getRow(2) != null) {
					coloumNum = sheet.getRow(2).getPhysicalNumberOfCells() + 1;
				} else if (sheet.getRow(3) != null) {
					coloumNum = sheet.getRow(3).getPhysicalNumberOfCells() + 1;
				} else if (sheet.getRow(4) != null) {
					coloumNum = sheet.getRow(4).getPhysicalNumberOfCells() + 1;
				} else if (sheet.getRow(5) != null) {
					coloumNum = sheet.getRow(5).getPhysicalNumberOfCells() + 1;
				} else if (sheet.getRow(6) != null) {
					coloumNum = sheet.getRow(6).getPhysicalNumberOfCells() + 1;
				} else if (sheet.getRow(7) != null) {
					coloumNum = sheet.getRow(7).getPhysicalNumberOfCells() + 1;
				}

				colWidth = sheet.getColumnWidth(i);// 获取列宽
				rows = sheet.getLastRowNum() + 1;// 不作处理时获取的行数
//				rows = sheet.getPhysicalNumberOfRows();// 获取物理行数
//				rows = readExcelValueRows(sheet);// 获取Excel工作簿的真实行数（非空行，即有数据的行）
//				rows = getExcelRealRow(sheet);// 获取excel表格的真实行数

				// 拆分sheet页生成新的Excel文件
				String filePath2 = fileName + "" + i + "." + fileEnd + "";
				copyFile(inputFilePath, filePath2);
				File file = new File(filePath2);
				XSSFWorkbook workbook2 = new XSSFWorkbook(new FileInputStream(file));
				int total2 = workbook2.getNumberOfSheets();
				for (int j = total2 - 1; j >= 0; j--) {
					if (i == j) {
						continue;
					}
					workbook2.removeSheetAt(j);
				}
				String filePath3 = fileName + "_" + i + "." + fileEnd + "";
				String pdfPath3 = fileName + "_" + i + ".pdf";
				File file1 = new File(filePath3);
				FileOutputStream fout = new FileOutputStream(filePath3);
				workbook2.write(fout);
				workbook2.close();
				fout.close();

				OpenOfficeConnection connection = new SocketOpenOfficeConnection(LOCAL_HOST, LOCAL_PORT);
				connection.connect();

				int rowsHeight = 0;
				if (rows < 9) {
					rowsHeight = rows * 650;
				} else if (rows >= 9 && rows <= 10) {
					rowsHeight = rows * 550;
				} else if (rows > 10 && rows <= 20) {
					rowsHeight = rows * 550;
				} else if (rows > 20 && rows <= 30) {
					rowsHeight = rows * 380;
				} else if (rows > 30 && rows <= 50) {
					rowsHeight = rows * 325;
				} else if (rows > 50 && rows <= 100) {
					rowsHeight = rows * 325;
				} else if (rows > 100 && rows <= 150) {
					rowsHeight = rows * 305;
				} else if (rows > 150 && rows <= 200) {
					rowsHeight = rows * 305;
				} else if (rows > 200 && rows <= 300) {
					rowsHeight = rows * 295;
				} else if (rows > 300 && rows <= 400) {
					rowsHeight = rows * 295;
				} else if (rows > 400 && rows <= 500) {
					rowsHeight = rows * 295;
				} else if (rows > 500 && rows <= 600) {
					rowsHeight = rows * 295;
				} else {
					rowsHeight = rows * 265;
				}

				int coloumWidth = (coloumNum * colWidth) * 3;
				if (coloumWidth <= 21000) {
					coloumWidth = 21000;
				} else if (coloumWidth > 21000 && coloumWidth <= 29700) {
					coloumWidth = 29700;
				} else if (coloumWidth > 29700 && coloumWidth <= 42000) {
					coloumWidth = 42000;
				} else {
					coloumWidth = 54000;
				}

//				System.out.println("总列数----------------" + coloumNum);
//				System.out.println("列宽----------------" + colWidth);
//				System.out.println("行数----------------" + rows);
//				System.out.println("总宽度----------------" + coloumWidth);
//				System.out.println("总行高----------------" + rowsHeight);

				ConverterDocument converterDocument = new ConverterDocument(connection, coloumWidth, rowsHeight);
				converterDocument.convert(new File(filePath3), new File(pdfPath3));

				connection.disconnect();

				file.delete();// 删除文件
				file1.delete();// 删除文件

				String pdfPath4 = pdfPath3.replace("/", File.separator);
				files = files + comma + "" + pdfPath4.substring(0, pdfPath4.length()) + "";
				comma = ",";
			}

			String[] filess = files.split(",");
			try {
				mergePdfFiles(filess, outputFilePath);
			} catch (MergePdfFilesException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

			workbook.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/**
	 * 文件复制
	 * 
	 * @param srcPathStr
	 * @param desPathStr
	 */
	private static void copyFile(String srcPathStr, String desPathStr) {
		try {
			FileInputStream fis = new FileInputStream(srcPathStr);
			FileOutputStream fos = new FileOutputStream(desPathStr);
			byte datas[] = new byte[1024 * 8];
			int len = 0;
			while ((len = fis.read(datas)) != -1) {
				fos.write(datas, 0, len);
			}
			fis.close();
			fos.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 合并pdf文件
	 * 
	 * @param files   要合並文件數組(絕對路徑如{ "e:\\1.pdf", "e:\\2.pdf" ,"e:\\3.pdf"})
	 * @param newfile 合並後新產生的文件絕對路徑如e:\\temp.pdf
	 * @return boolean 產生成功返回true, 否則返回false
	 * @throws MergePdfFilesException
	 */
	public static boolean mergePdfFiles(String[] files, String newfile) throws MergePdfFilesException {
		boolean retValue = false;
		Document document = null;
		try {
			document = new Document(new PdfReader(files[0]).getPageSize(1));
			PdfCopy copy = new PdfCopy(document, new FileOutputStream(newfile));
			document.open();
			for (int i = 0; i < files.length; i++) {
				PdfReader reader = new PdfReader(files[i]);
				int n = reader.getNumberOfPages();
				for (int j = 1; j <= n; j++) {
					document.newPage();
					PdfImportedPage page = copy.getImportedPage(reader, j);
					copy.addPage(page);
				}

				File file = new File(files[i]);
				file.delete();
			}
			retValue = true;
		} catch (Exception e) {
			e.printStackTrace();
			throw new MergePdfFilesException("合并pdf失败");
		} finally {
			document.close();
		}
		return retValue;
	}

}
